using System;
using System.IO;
using System.Reflection;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Configuration;
using Nxg.Blog.Core;

namespace Nxg.Blog.Web.Features {
    public class StartupFilter : IStartupFilter {
        private readonly DataContext _db;
        private readonly IConfiguration _conf;

        public StartupFilter (DataContext db, IConfiguration conf) {
            _conf = conf;
            _db = db;
        }

        private static string sql = @"DROP TABLE IF EXISTS 'Author';
CREATE TABLE 'Author' (
  'Id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  'HashedPassword' TEXT NOT NULL,
  'Email' TEXT NOT NULL,
  'DisplayName' TEXT NOT NULL,
  'Roles' TEXT NOT NULL
);

DROP TABLE IF EXISTS 'Category';
CREATE TABLE 'Category' (
  'Id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  'Name' TEXT NOT NULL,
  'DisplayName' TEXT NOT NULL
);

DROP TABLE IF EXISTS 'Comment';
CREATE TABLE 'Comment' (
  'Id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  'Content' TEXT NOT NULL,
  'NickName' TEXT NOT NULL,
  'Email' TEXT NOT NULL,
  'SiteUrl' TEXT,
  'CreatedTime' TEXT NOT NULL,
  'PostId' INTEGER NOT NULL,
  'IPAddress' TEXT
);


DROP TABLE IF EXISTS 'Post';
CREATE TABLE 'Post' (
  'Id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  'Title' TEXT NOT NULL,
  'TitleSlug' TEXT NOT NULL,
  'ViewCount' INTEGER NOT NULL,
  'MarkDown' TEXT NOT NULL,
  'Content' TEXT NOT NULL,
  'Status' INTEGER NOT NULL,
  'PubDate' TEXT NOT NULL,
  'Tags' TEXT NOT NULL,
  'AuthorDisplayName' TEXT NOT NULL,
  'AuthorEmail' TEXT NOT NULL,
  'CategoryId' INTEGER
);

DROP TABLE IF EXISTS 'SpanHash';
CREATE TABLE 'SpanHash' (
  'Id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  'PostKey' TEXT NOT NULL,
  'Hash' TEXT NOT NULL,
  'Pass' INTEGER NOT NULL,
  'CreatedTime' TEXT NOT NULL
);

DROP TABLE IF EXISTS 'Tag';
CREATE TABLE 'Tag' (
  'Id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  'Slug' TEXT NOT NULL,
  'Name' TEXT NOT NULL,
  'PostCount' INTEGER NOT NULL
);
";

        public Action<IApplicationBuilder> Configure (Action<IApplicationBuilder> next) {

            var connectionString = _conf.GetValue<string> ("ConnectionString");

            using var conn = new SqliteConnection (connectionString);
            conn.Open ();
            using var command = conn.CreateCommand ();

            command.CommandText = "SELECT COUNT(1) FROM sqlite_master where type='table'";

            var tableCount = int.Parse (command.ExecuteScalar ().ToString ());

            if (tableCount == 0) {
                command.CommandText = sql;
                command.ExecuteNonQuery ();
            }

            return next;
        }
    }
}